﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Oracle.DataAccess;

using EntitiesLayer.Entities;
using System.Data;
using System.Data.OracleClient;


namespace DataLayer.DAO
{
    public class AreaDAO
    {
        private Connection.Connection cn;
        private OracleConnection objCn;

        public AreaDAO()
        {
            cn = new Connection.Connection();
            objCn = cn.Conecta();
        }

        //public List<Area> consultarArea(string codigoArea)
        //{
        //    List<Area> list = new List<Area>();

        //    using (OracleCommand cmd = new OracleCommand("BUSCARAREAXCODIGO", objCn))
        //    {
        //        cmd.CommandType = CommandType.StoredProcedure;

        //        OracleParameter refParameter = new OracleParameter();
        //        refParameter.ParameterName = "RECORDSET";
        //        refParameter.OracleType = OracleType.Cursor;
        //        refParameter.Direction = ParameterDirection.Output;

        //        OracleParameter codigoParameter = new OracleParameter();
        //        codigoParameter.ParameterName = "v_codigoArea";
        //        codigoParameter.OracleType = OracleType.NVarChar;
        //        codigoParameter.Size = 10;
        //        codigoParameter.Direction = ParameterDirection.Input;
        //        codigoParameter.Value = codigoArea;

        //        cmd.Parameters.Add(refParameter);
        //        cmd.Parameters.Add(codigoParameter);
                
        //        try
        //        {
        //            objCn.Open();

        //            OracleDataReader dr = cmd.ExecuteReader();

        //            while (dr.Read())
        //            {
        //                Area a = new Area();
        //                a.Cod_area = Convert.ToString(dr[0]);
        //                a.Des_area = Convert.ToString(dr[1]);
        //                a.Jef_area = Convert.ToString(dr[2]);
                      
        //                list.Add(a);
        //            }
        //        }
        //        catch (Exception ex)
        //        {
        //            objCn.Dispose();
        //        }
        //        finally
        //        {
        //            objCn.Close();
        //        }
        //    }
        //    return list;
        //}

        public DataTable consultarAreaDetalle(string codigoSNC)
        {
            OracleCommand cmd = new OracleCommand("BUSCARAREASNC", objCn);
            cmd.CommandType = CommandType.StoredProcedure;

            OracleParameter refParameter = new OracleParameter();
            refParameter.ParameterName = "RECORDSET";
            refParameter.OracleType = OracleType.Cursor;
            refParameter.Direction = ParameterDirection.Output;

            OracleParameter codigoParameter = new OracleParameter();
            codigoParameter.ParameterName = "R_COD_SNC";
            codigoParameter.OracleType = OracleType.NVarChar;
            codigoParameter.Size = 30;
            codigoParameter.Direction = ParameterDirection.Input;
            codigoParameter.Value = codigoSNC;

            cmd.Parameters.Add(refParameter);
            cmd.Parameters.Add(codigoParameter);

            OracleDataAdapter da = new OracleDataAdapter();
            da.SelectCommand = cmd;
            DataTable dt = new DataTable();            
            da.Fill(dt);

            return dt;          
        }

 


        public Area buscarArea(string codigoArea)
        {
            Area a = new Area();

            using (OracleCommand cmd = new OracleCommand("BUSCARAREAXCODIGO", objCn))
            {
                cmd.CommandType = CommandType.StoredProcedure;

                OracleParameter refParameter = new OracleParameter();
                refParameter.ParameterName = "RECORDSET";
                refParameter.OracleType = OracleType.Cursor;
                refParameter.Direction = ParameterDirection.Output;

                OracleParameter codigoParameter = new OracleParameter();
                codigoParameter.ParameterName = "v_codigoArea";
                codigoParameter.OracleType = OracleType.NVarChar;
                codigoParameter.Size = 10;
                codigoParameter.Direction = ParameterDirection.Input;
                codigoParameter.Value = codigoArea;

                cmd.Parameters.Add(refParameter);
                cmd.Parameters.Add(codigoParameter);

                try
                {
                    objCn.Open();

                    OracleDataReader dr = cmd.ExecuteReader();

                    while (dr.Read())
                    {
                        a.Cod_area = Convert.ToString(dr[0]);
                        a.Des_area = Convert.ToString(dr[1]);
                        a.Jef_area = Convert.ToString(dr[2]);
                        a.Email_area = (dr[3]!=null)?Convert.ToString(dr[3]):"";
 

                    }
                }
                catch (Exception ex)
                {
                    objCn.Dispose();
                }
                finally
                {
                    objCn.Close();
                }
            }
            return a;
        }

      
    }
}
